package util;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class SqlUtil {

	/**
	 * 获取表里某个字段 最大数值+1
	 * 
	 * @param conn
	 *            数据库连接
	 * @param table
	 *            表名
	 * @param filed
	 *            字段名（ID）
	 * @return 最大数值+1
	 */
	public static Integer getNextId(Connection conn, String table, String filed) {
		StringBuffer sb = new StringBuffer();
		sb.append("select max(").append(filed).append(") from ")
				.append(table);
		Statement stmt = null;
		ResultSet rs = null;
		Integer nextId = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sb.toString());
			if (rs.next()) {
				nextId = rs.getInt(1)+1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, stmt);
		}
		return nextId;
	}

	/**
	 * 组装添加语句
	 * 
	 * @param table
	 *            表名
	 * @param cloms
	 *            要添加字段的数组
	 * @param type
	 *            字段类型的数组(1:int;2:String;3:其他类型)
	 * @param values
	 *            字段值的数组
	 * @return 组装好的SQL语句
	 */
	public static String assemblyAdd(String table, String[] cloms, int[] type,
			String[] values) {
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(table).append("(");
		for (String clom : cloms) {// 组装字段
			sb.append(clom).append(",");
		}
		sb.replace(sb.length() - 1, sb.length(), ") values(");
		for (int i = 0; i < values.length; i++) {// 组装字段值
			if (type[i] == 1) // int类型
				sb.append(values[i]).append(",");
			else if (type[i] == 2) // String类型
				sb.append("'").append(values[i]).append("',");
			else
				// 其他类型
				sb.append("'").append(values[i]).append("',");
		}
		sb.replace(sb.length() - 1, sb.length(), ")");
		return sb.toString();
	}

	/**
	 * 组装更新语句
	 * 
	 * @param table
	 *            表名
	 * @param cloms
	 *            要添加字段的数组
	 * @param type
	 *            字段类型的数组(1:int;2:String;3:其他类型)
	 * @param values
	 *            字段值的数组
	 * @param condition
	 *            条件语句
	 * @return 组装好的SQL语句
	 */
	public static String assemblyUpdate(String table, String[] cloms,
			int[] type, String[] values, String condition) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(table).append(" set ");
		for (int i = 0; i < cloms.length; i++) {// 组装字段值
			sb.append(cloms[i]).append("=");
			if (type[i] == 1) // int类型
				sb.append(values[i]).append(",");
			else if (type[i] == 2) // String类型
				sb.append("'").append(values[i]).append("',");
			else
				// 其他类型
				sb.append("'").append(values[i]).append("',");
		}
		sb.deleteCharAt(sb.length() - 1);
		if (condition != null) // 条件限制
			sb.append(" where ").append(condition);
		return sb.toString();
	}

	/**
	 * 组装分页语句
	 * @param sql 待组装的SQL
	 * @param page 第几页
	 * @param rows 每页显示的数量
	 * @return 组装好的分页SQL
	 */
	public static String pageSql(String sql, int page, int rows) {
		StringBuffer sb = new StringBuffer();
		int start = page * rows;
		int end = start - rows+1;
		sb.append("SELECT * FROM (SELECT ZXW.*, ROWNUM RN FROM (").append(sql)
				.append(") ZXW WHERE ROWNUM <= ").append(start).append(
						") WHERE RN >= ").append(end);
		return sb.toString();
	}
	
	/**
	 * @Title: pageCount 
	 * @Description: 查询总记录数
	 * @param @param sql 查询所有的SQL
	 * @param @param field 求和字段
	 * @param @param page 分页对象
	 * @param @param conn 
	 * @param @return
	 * @return PageVo 返回分页对象
	 * @throws
	 */
	

	public static void main(String[] args) {
		Connection conn = JdbcUtil.getConn();
//		String[] cloms = { "user_password", "user_status" };
//		int[] type = { 2, 2 };
//		String[] values = { "123", "1" };
//		String s = assemblyUpdate("tb_user", cloms, type, values, "user_id=1");
//		System.out.println(s);
//		Statement st = JdbcUtil.getStmt(conn);
//		boolean b = JdbcUtil.executeUpdate_Delete_Insert(s, st);
//		System.out.println(b);
		/*
		 * 添加
		 */
		// String[] str =
		// {"user_id","user_account","user_password","user_name","user_tel","user_addr","user_card","user_phone","user_status"};
		// int[] strss = {1,2,2,2,2,2,2,2,2};
		// String[] strs = {getNextId(conn, "tb_user",
		// "user_id"),"admin","admin","zeng","234139","one price","19374837473937849334","139992847294","0"};
		// String s = assemblyAdd("tb_user", str,strss, strs);
		// System.out.println(s);
		// Statement st = JdbcUtil.getStmt(conn);
		// boolean b = JdbcUtil.executeUpdate_Deltte_Insert(s, st);
		// System.out.println(b);
		
		int i = SqlUtil.getNextId(conn, "tb_result", "result_id");
		System.out.println(i);
	}

}
